﻿#!/usr/bin/perl

use strict;  
use warnings;
use DBI;

my $host="10.41.111.213";
my $user="omc";
my $passwd="omc";

# connect to db
my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=oss;port=1521", $user, $passwd)
 or die "Can't connect to database: $DBI::errstr";

open(OutFile, "<nokrww_table.txt") || die("Can not open the file!$!n");
my @line = <OutFile>;
close(OutFile);

open(InFile,">data.txt") || die("Can not open the file!$!n");

while(@line){
	
	my $tableName = shift(@line);
	$tableName =~ s/^\s+//;
	$tableName =~ s/\s+$//;
	
	next unless $tableName;
	print "---$tableName---\n";
	print InFile "---".$tableName."---\n";
	
	# output the column name
	my $colCMD = "SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = '$tableName' ORDER BY COLUMN_ID";
	my $sth=$dbh->prepare($colCMD);
	$sth->execute;
	
	while(my @row = $sth->fetchrow_array) {
		print InFile sprintf("%-35s", $row[0]);
	}
	
	print InFile "\n";
	
	
	# output the data
	my $dataCMD = "SELECT * FROM NOKRWWRAW.$tableName ORDER BY PERIOD_START_TIME DESC";
	$sth=$dbh->prepare($dataCMD);
	$sth->execute;

	my $table = $sth->fetchall_arrayref or die "$sth->errstr/n";
	my($i, $j);

	for $i ( 0 .. $#{$table} ) {
		for $j ( 0 .. $#{$table->[$i]} ) {
			my $value = $table->[$i][$j];
			unless ($value){
				$value = "(null)";
			}
			
			print InFile sprintf("%-35s", $value);
		}
		print InFile "\n";
	}
	
	print InFile "\n\n";
	
	$sth->finish;
}

$dbh->disconnect;
close(InFile);
